cursor: pin S wait on X
A session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.
Detailed Description​
Each query used in the database is parsed by Oracle for correctness and then stored an area of the shared pool
as a cursor. Oracle controls access to this and other areas of shared memory via a method called a 'mutex' (mutually exclusive). In this case, access to a specific cursor in Shared mode has been requested, but another session currently has an eXclusive lock on it and we haver to wait for it to be released.
How to reduce this wait​
-
High rates of parsing SQL queries can be an issue here. During parsing the query is checked to see if it already exists in memory. Where client connections pass in string literals, a high number of very similar versions of the SQL can accumulate in the shared pool and make it difficult for Oracle to manage.
SELECT * FROM emp WHERE ename='Mr Insights'
- this is a string literal query and should be avoided.
SELECT * FROM emp WHERE ename=:v_username
- is a bind variable query and will reduce the parsing overhead.
-
Issues can also be caused by automatic shared memory management or setting the parallel query paramter
parallel_degree_policy
toADAPTIVE
.
Additional Links​
- Oracle Reference -
cursor: pin S wait on X
- Oracle Base - Literals, Substitution Variables and Bind Variables
- Cursor: Pin S Wait On X and library cache lock Wait Event Solution
cursor: pin S wait on X
Search online​
If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.